/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.web.daoimpl;

import com.web.dao.CourseDao;
import com.web.entity.Course;
import com.web.util.DatabaseUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author liqianru
 */
public class CourseDaoImpl extends BaseDaoImpl implements CourseDao {

    @Override
    public List<Course> getallCourse() {
        List<Course> list = new ArrayList<>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rset = null;
        con = DatabaseUtil.getConnection();
        try {
            ps = con.prepareStatement("select Course.CourseID,CourseName,Teachers.Tname,Teachers.Tid,ClassName from Course,Teachers,Class where Course.Tid=Teachers.Tid and Teachers.ClassID=Class.ClassID");
            rset = ps.executeQuery();
            while (rset.next()) {
                list.add(new Course(
                        rset.getInt(1),
                        rset.getString(2),
                        rset.getInt(4),
                        rset.getString(3),
                        rset.getString(5)
                ));
            }
        } catch (SQLException ex) {
            Logger.getLogger(BaseDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DatabaseUtil.close(rset, ps, con);
        }
        return list;
    }

    @Override
    public int addCourse(Course c) {
        int rows = 0;
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rset = null;
        con = DatabaseUtil.getConnection();
        try {
            ps = con.prepareStatement("insert into Course values(?,?,?)");
            ps.setInt(1, c.getCourseId());
            ps.setString(2, c.getCourseName());
            ps.setInt(3, c.getTid());
            rows = ps.executeUpdate();

        } catch (SQLException ex) {
            Logger.getLogger(CourseDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rows;
    }

    @Override
    public List<Course> getBykeyword(String keyword) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rset = null;
        con = DatabaseUtil.getConnection();
        List<Course> list = new ArrayList<>();
        try {
            ps = con.prepareStatement("select Course.CourseID,CourseName,Teachers.Tname from Course,Teachers where Course.Tid=Teachers.Tid and (Course.CourseID like ? or CourseName like ? or Teachers.Tname like ? ) ");
            ps.setString(1, "%" + keyword + "%");
            ps.setString(2, "%" + keyword + "%");
            ps.setString(3, "%" + keyword + "%");
            rset = ps.executeQuery();
            while (rset.next()) {
                list.add(new Course(
                        rset.getInt(1),
                        rset.getString(2),
                        rset.getString(3)
                ));
            }
        } catch (SQLException ex) {
            Logger.getLogger(CourseDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DatabaseUtil.close(rset, ps, con);
        }
        return list;
    }

    @Override
    public int deleteCourse(int courseid) {
        int[] i = new int[]{courseid, courseid, courseid};
        return update("delete from TestQuestions where CourseID=? delete from Schedule where CourseID=? delete from Course where CourseID=?", i);
    }

    @Override
    public int updateCourse(int courseid, int tid) {
        int[] i = new int[]{tid, courseid};
        return update("update Course set Tid=? where CourseID=?", i);
    }

    @Override
    public List<Course> getsBykeyword(String keyword) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rset = null;
        con = DatabaseUtil.getConnection();
        List<Course> list = new ArrayList<>();
        try {
            ps = con.prepareStatement("select c.ClassName,cou.CourseName,t.Tname,cou.CourseID,s.Tid from Schedule s,Class c,Teachers t,Course cou where (s.ClassID=c.ClassID and s.CourseID=cou.CourseID and s.Tid=t.Tid)and(c.ClassName like ? or cou.CourseName like ? or t.Tname like ?) ");
            ps.setString(1, "%" + keyword + "%");
            ps.setString(2, "%" + keyword + "%");
            ps.setString(3, "%" + keyword + "%");
            rset = ps.executeQuery();
            while (rset.next()) {
                list.add(new Course(
                        rset.getInt(4),
                        rset.getString(2),
                        rset.getInt(5),
                        rset.getString(3),
                        rset.getString(1)
                ));
            }
        } catch (SQLException ex) {
            Logger.getLogger(CourseDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DatabaseUtil.close(rset, ps, con);
        }
        return list;
    }

    @Override
    public int updateSchedule(String classid, int tid, int courseid) {
        int[] i = new int[]{tid, courseid};
        int row1 = update("update Schedule set ClassID=?,Tid=? where CourseID=?", i, classid);
        int row2 = update("update Course set Tid=? where CourseID=? ", i);
        return row1 + row2;
    }

    @Override
    public List<Course> getallSchedule() {
        List<Course> list = new ArrayList<>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rset = null;
        con = DatabaseUtil.getConnection();
        try {
            ps = con.prepareStatement("select c.ClassName,cou.CourseName,t.Tname,cou.CourseID,s.Tid from Schedule s,Class c,Teachers t,Course cou where s.ClassID=c.ClassID and s.CourseID=cou.CourseID and s.Tid=t.Tid");
            rset = ps.executeQuery();
            while (rset.next()) {
                list.add(new Course(
                        rset.getInt(4),
                        rset.getString(2),
                        rset.getInt(5),
                        rset.getString(3),
                        rset.getString(1)
                ));
            }
        } catch (SQLException ex) {
            Logger.getLogger(BaseDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DatabaseUtil.close(rset, ps, con);
        }
        return list;
    }

    @Override
    public int addSchedule(String classid, int tid, int courseid) {
        int rows = 0;
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rset = null;
        System.out.println(classid + "--" + tid + "--" + courseid);
        con = DatabaseUtil.getConnection();
        try {
            ps = con.prepareStatement("insert into Schedule values(?,?,?)");
            ps.setString(1, classid);
            ps.setInt(2, courseid);
            ps.setInt(3, tid);
            rows = ps.executeUpdate();

        } catch (SQLException ex) {
            Logger.getLogger(CourseDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rows;
    }

    @Override
    public int deleteSchedule(int courseid) {
        int[] i = new int[]{courseid};
        return update(" delete from Schedule where CourseID=?", i);
    }

    @Override
    public List<Course> getoneSchedule(String classid) {
        List<Course> list = new ArrayList<>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rset = null;
        con = DatabaseUtil.getConnection();
        try {
            ps = con.prepareStatement("select c.ClassName,cou.CourseName,t.Tname,cou.CourseID,s.Tid from Schedule s,Class c,Teachers t,Course cou "
                    + "where (s.ClassID=c.ClassID and s.CourseID=cou.CourseID and s.Tid=t.Tid) and s.ClassID=?");
            ps.setString(1, classid);
            rset = ps.executeQuery();
            while (rset.next()) {
                list.add(new Course(
                        rset.getInt(4),
                        rset.getString(2),
                        rset.getInt(5),
                        rset.getString(3),
                        rset.getString(1)
                ));
            }
        } catch (SQLException ex) {
            Logger.getLogger(BaseDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DatabaseUtil.close(rset, ps, con);
        }
        return list;
    }

    @Override
    public Course getOneCourseNameByCourseID(int courseid) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Course c = null;
        try {
            con = DatabaseUtil.getConnection();
            ps = con.prepareStatement("select * from Course where CourseID=?");
            ps.setInt(1, courseid);
            rs = ps.executeQuery();
            while (rs.next()) {
                c = new Course(
                        rs.getInt(1),
                        rs.getString(2),
                        rs.getInt(3)
                );
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            DatabaseUtil.close(rs, ps, con);
        }
        return c;
    }

    public List<Course> getallBycoursename(String coursename) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rset = null;
        con = DatabaseUtil.getConnection();
        List<Course> courseidList = new ArrayList<>();
        try {
            ps = con.prepareStatement("select CourseID from Course where CourseName=?");
            ps.setString(1, coursename);
            rset = ps.executeQuery();
            while (rset.next()) {
                courseidList.add(new Course(rset.getInt(1)));
            }
        } catch (SQLException ex) {
            Logger.getLogger(CourseDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DatabaseUtil.close(rset, ps, con);
        }
        return courseidList;
    }

    @Override
    public int isrepeatCourseid(int courseid) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rset = null;
        con = DatabaseUtil.getConnection();
        try {
            ps = con.prepareStatement("select *  from Course where CourseID=?");
            ps.setInt(1, courseid);
            rset = ps.executeQuery();
            if (rset.next()) {
                return 1;

            }
        } catch (SQLException ex) {
            Logger.getLogger(CourseDaoImpl.class
                    .getName()).log(Level.SEVERE, null, ex);
        } finally {
            DatabaseUtil.close(rset, ps, con);
        }
        return 0;
    }

}
